package io.chenglicun.modules.devtools.utils;

import io.chenglicun.common.utils.DateUtils;
import lombok.Cleanup;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ImportExcel2DB {
    private final String moduleName = "clc";

    /**
     * @param file     excel文件
     * @param type     0：单文件单表，一个sheet，根据文件名对应实体类名，1：单文件多表，多个sheet，根据sheet名对应实体类名
     * @param firstRow
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws InstantiationException
     */
    public void importExcel2DB(MultipartFile file, int type, int firstRow) throws IOException, ClassNotFoundException, NoSuchFieldException, IllegalAccessException, NoSuchMethodException, InvocationTargetException, InstantiationException {

//        exFun();
        InputStream inputStream = file.getInputStream();//获取前端传递过来的文件对象，存储在“inputStream”中
        String fileName = file.getOriginalFilename();//获取文件名
        Workbook workbook = null; //用于存储解析后的Excel文件

        try {
            //判断文件扩展名为“.xls还是xlsx的Excel文件”,因为不同扩展名的Excel所用到的解析方法不同
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if (".xls".equals(fileType)) {
                workbook = new HSSFWorkbook(inputStream);//HSSFWorkbook专门解析.xls文件
            } else if (".xlsx".equals(fileType)) {
                workbook = new XSSFWorkbook(inputStream);//XSSFWorkbook专门解析.xlsx文件
            }
            int sheetsCount = workbook.getNumberOfSheets();
            for (int i = 0; i < sheetsCount; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                // 通过反射获取
                // 通过sheetName对应实体类名
                if (type == 1)
                    data2DB(sheet, "", firstRow);
                else if (type == 0) // 通过文件名对应实体类名
                    data2DB(sheet, fileName.substring(0, fileName.lastIndexOf(".")), firstRow);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            inputStream.close();
            workbook.close();
        }
    }

    public void data2DB(Sheet sheet, String entityName, int firstRow) throws RuntimeException {
        String sheetName = StringUtils.isNotEmpty(entityName) ? entityName : sheet.getSheetName();
        // 通过反射获取类和属性名
        Class<?> service = null;
        String com = "io.chenglicun.modules." + moduleName + ".service.impl." + sheetName + "ServiceImpl";
        try {
            service = Class.forName(com);
        } catch (ClassNotFoundException e) {
            System.out.println(sheetName);
            throw new RuntimeException(e);
        }
        Object bean = SpringBeanUtils.getBean(service);
        Method[] methods = service.getSuperclass().getMethods();
        Method insert = null;
        for (Method method : methods) {
            if ("insert".equals(method.getName())) {
                insert = method;
                break;
            }
        }
        if (insert == null) {
            throw new RuntimeException("未获取到导入方法");
        }
        Class<?> clazz = null;
        try {
            clazz = Class.forName("io.chenglicun.modules." + moduleName + ".entity." + sheetName + "Entity");
        } catch (ClassNotFoundException e) {
            System.out.println(sheetName);
            throw new RuntimeException(e);
        }
        Row row0 = sheet.getRow(0);
        for (int i = firstRow; i <= sheet.getLastRowNum(); i++) {
            Constructor constructor = null;
            try {
                constructor = clazz.getConstructor();
            } catch (NoSuchMethodException e) {
                System.out.println(sheetName);
                throw new RuntimeException(e);
            }
            Object entity = null;
            try {
                entity = constructor.newInstance();
            } catch (InstantiationException | IllegalAccessException | InvocationTargetException e) {
                System.out.println(sheetName);
                throw new RuntimeException(e);
            }
            boolean skip = false;
            // 循环属性赋值
            for (int j = 0; j < row0.getLastCellNum(); j++) {
                String fieldName = row0.getCell(j).getStringCellValue();
                Field field = null;
                try {
//                    if ("id".equals(fieldName)) {
//                        field = entity.getClass().getSuperclass().getDeclaredField("id");
//                    } else {
                    field = entity.getClass().getDeclaredField(fieldName);
//                    }
                } catch (NoSuchFieldException e) {
                    System.out.println(sheetName);
                    throw new RuntimeException(e);
                }
                field.setAccessible(true);
                Cell cell = sheet.getRow(i).getCell(j);
//                if (j == 0 && (cell == null || StringUtils.isEmpty(cell.getStringCellValue()))) {
//                    skip = true;
//                    break;
//                }
                Object valueObj = null;   // 将字符串转换为属性类型
                if (cell == null)
                    valueObj=null;
                String v = "";
                try {
                    v = cell.getStringCellValue();
                } catch (Exception es) {
                    try {
                        v = cell.getNumericCellValue()+"";
                    }catch (Exception en){
                        try{
                            v=cell.getDateCellValue().toString();
                        }catch (Exception ed){
                            valueObj=null;
                        }
                    }
                }
                try {
                    if ("java.lang.String".equals(field.getType().getTypeName())) {
                        field.set(entity, v);
                        continue;
                    }
//                        v = exFieldFun(v, field);
                    valueObj = convertToPropertyType(v, field.getType());
                } catch (InstantiationException | IllegalAccessException | InvocationTargetException |
                         NoSuchMethodException | ParseException | NullPointerException e) {
                    System.out.println(sheetName);
                    System.out.println(field.getType());
                    System.out.println(fieldName);
                    System.out.println(v);
                    throw new RuntimeException(e);
                }
                try {
                    field.set(entity, valueObj);
                } catch (IllegalAccessException e) {
                    System.out.println(sheetName);
                    throw new RuntimeException(e);
                }
            }
            // 插入数据
            try {
                if (skip) continue;
                insert.invoke(bean, entity);
            } catch (IllegalAccessException | InvocationTargetException e) {
                System.out.println(sheetName);
                System.out.println(entity);
                System.out.println(e.getMessage());
            }
        }
    }

    private static <T> T convertToPropertyType(String strValue, Class<T> type) throws NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException, ParseException {
        String s = type.getTypeName();
        if ("java.util.Date".equals(s)) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return (T) sdf.parse(strValue);
        } else if ("java.math.BigDecimal".equals(s)) {
            return (T) BigDecimal.valueOf(Double.parseDouble(strValue));
        } else {
            return type.getConstructor(strValue.getClass()).newInstance(strValue);// 利用构造函数转换类型
        }

    }


    Map<String, Map<String, String>> map = new HashMap<>();

    /**
     * 扩展方法，与属性扩展方法配合使用，用以处理特殊属性
     */
//    private void exFun() throws ClassNotFoundException {
//        SysDictTypeDao dictTypeDao = SpringBeanUtils.getBean((Class<SysDictTypeDao>) Class.forName("io.jiuse.modules.sys.dao.SysDictTypeDao"));
//        SysDictDataDao dictDataDao = SpringBeanUtils.getBean((Class<SysDictDataDao>) Class.forName("io.jiuse.modules.sys.dao.SysDictDataDao"));
//        // 获取字典  contractType
//        Map<String, String> dicMap = new HashMap<>();
//        SysDictTypeEntity contractType = dictTypeDao.selectOne(new QueryWrapper<SysDictTypeEntity>().lambda().eq(SysDictTypeEntity::getDictType, "contract_type").last("limit 0,1"));
//        Long dictId = contractType.getId();
//        List<SysDictDataEntity> sysDictDataEntities = dictDataDao.selectList(new QueryWrapper<SysDictDataEntity>().lambda().eq(SysDictDataEntity::getDictTypeId, dictId));
//        for (SysDictDataEntity sysDictDataEntity : sysDictDataEntities) {
//            dicMap.put(sysDictDataEntity.getDictLabel(), sysDictDataEntity.getDictValue());
//        }
//        map.put("contractType", dicMap);
//    }

    /**
     * 属性扩展方法，主要处理特定属性的值，
     * 比如有些值用的是字典，存数字，但是表格中是字符串，这里处理
     *
     * @return
     */
//    private String exFieldFun(String v, Field field) {
//        String fieldName = field.getName();
//        switch (fieldName) {
//            case "isFinish":
//            case "invoiceStatus":
//            case "paymentStatus":
//                Integer value = ContractFieldEnum.get(v).getFiled();
//                return value + "";
//            case "contractType":
//                String rv = map.get("contractType").get(v);
//                return rv;
//            case "singingTime":
//                return v + "-1-1 00:00:00";
//            case "startDate":
//            case "endDate":
//            case "invoiceDate":
//            case "paymentDate":
//                return v + " 00:00:00";
//            case "password":
//                return PasswordUtils.encode(v);
//        }
//        return v;
//    }
    public void exportExcel(HttpServletResponse response, String fileName, List<String> titles, List<Map<String, String>> dataList) throws IOException {

        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtils.format(new Date());
        }

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet = book.createSheet();
        HSSFRow row0 = sheet.createRow(0);
        // 设置标题
        for (int i = 0; i < titles.size(); i++) {
            HSSFCell cell = row0.createCell(i);
            cell.setCellValue(titles.get(i));
        }
        // 填充数据
        for (int i = 0; i < dataList.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < titles.size(); j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(dataList.get(i).get(titles.get(j)));
            }
        }
        OutputStream out = response.getOutputStream();
        book.write(out);
        out.close();
    }
}
